Automating Physical Database Design: An Extensible Approach

In a high-level query language such as SQL, queries yield the same result no matter how the logical schema is physically implemented. Nevertheless, a query's cost can vary by orders of magnitude among di erent physical implementations of the same logical schema, even with the most modern query optimizers. Therefore, designing a low-cost physical implementation is an important pragmatic problem|one that requires a sophisticated understanding of physical design options and query strategies, and that involves estimating query costs, a tedious and error-prone process when done manually. We have devised a simple framework for automating physical design in relational or post-relational DBMSs and in database programming languages. Within this framework, design options are uniformly represented as \features", and designs are represented by \con ict"-free sets of features. (Mutually exclusive features con ict. An example would be two primary indexes on the same table.) The uniform representation of design options as features accommodates a greater variety of design options than previous approaches; adding a new design option (e.g. a new index type) merely entails characterizing it as a feature with appropriate parameters. We propose an approximation algorithm, based on this framework, that nds low-cost physical designs. In an initial phase, the algorithm examines the logical schema, data statistics, and queries, and generates \useful features"|features that might reduce query costs. In a subsequent phase, the algorithm uses the DBMS's cost estimates to nd \best features"|features that belong to the lowestcost designs for each individual query. Finally, the algorithm searches among con ict-free subsets of the best features of all the queries to nd organizations with low global cost estimates. We have implemented a prototype physical design assistant for the INGRES relational DBMS, and we evaluate its designs for several benchmarks, including ASSSAP. Our experiments with the prototype show that it can produce good designs, and that the critical factor limiting their quality is the accuracy of query cost estimates. The prototype implementation isolates dependencies on INGRES, permitting our framework to produce design assistants for a wide range of relational, nested-relational, and object-oriented DBMSs. To my parents, Barbara L. Rozen and Jerome G. Rozen, Jr., and to my daughter, Kate.